package com.artfii.fluentsql.example;

import com.artfii.fluentsql.core.jdbc.FluentConnection;
import com.artfii.fluentsql.core.jdbc.FluentSqlQuery;
import com.artfii.fluentsql.core.jdbc.Lq;
import com.artfii.fluentsql.core.jdbc.Qe;
import com.artfii.fluentsql.core.mock.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * Func:   :
 *
 * @author : Lqf(leeton)
 * @date : 2021/5/28.
 */
@Service
public class TestUserService implements ITestUserService {
    @Resource
    private FluentConnection fluentConnection;
    @Resource
    private JdbcTemplate jdbcTemplate;

    public User getUserById(Integer id) {
        User rUser= new Lq<>(User.class).andEq(User::getId,id).debug().to();
        return rUser;
    }

    public List<User> getNew10() {
        List<User> userList= new Lq<>(User.class)
                .desc(User::getId).limit(0,9).debug().toList();
        return userList;
    }

    public List<User> getAll() {
        long s = System.currentTimeMillis();
        List<User> userList= new Lq<>(User.class).toList();
        System.err.println("used times:" + (System.currentTimeMillis() - s));
        return userList;
    }


    @Transactional
    public String addUser(){
        int rows = 0;
        for (int i = 0; i < 2000; i++) {
            User u1 = new User(i, "leeton"+i, 19, new BigDecimal(19.33), 1, "13680018001")
                    .ofDefault(i);
            Lq<User> lq = new Lq<>(User.class);
           lq.debug().toSave(u1);
           rows++;
        }
//        int i= 1/0;
        return "insert row:"+rows;
    }

    public Integer batchSave(){
        long s = System.currentTimeMillis();
        int rows = 0;
        int max = 1000;
        List<User> saveList = new ArrayList<>(max);
        for (int i = 0; i < max; i++) {
            User u1 = new User(i, "leeton"+i, null, new BigDecimal(19.33), 1, "13680018001").ofDefault(i);
            saveList.add(u1);
        }
        rows = new Lq<>(User.class).toBatchInsert(saveList);
        System.err.println("used times:" + (System.currentTimeMillis() - s));
        return rows;
    }

    public Integer batchUpdate(){
        long s = System.currentTimeMillis();
        int rows = 0;
        int max = 1000;
        List<User> saveList = new ArrayList<>(max);
        for (int i = 0; i < max; i++) {
            User u1 = new User(i, "leeton"+i, null, new BigDecimal(i), 1, "13680018001").ofDefault(i);
            saveList.add(u1);
        }
        rows = new Qe<>(User.class,fluentConnection).toBatchUpdate(saveList);
//        fluentConnection.commit();
        System.err.println("used times:" + (System.currentTimeMillis() - s));
        return rows;
    }

    @Transactional
    public String add2(){
        User u1 = new User(1, "alice", null, new BigDecimal(18.56), 1, "13680018001").ofDefault(2);
        Lq<User> lq = new Lq<>(User.class);
        int rows = lq.notSkipNullVol().debug().toSave(u1);
        return "insert row:"+rows;
    }

    @Transactional
    public Integer update(Integer id) {
        User u1 = new User();
        u1.setId(id);
        u1.setDeptId(777);
        u1.setAge(100);
        return new Lq(User.class).toUpdate(u1);
    }

    @Transactional
    public Integer updateOfJdbc(Integer id) {
        return jdbcTemplate.update("update user set dept_id=?,age=? where id=?",9999,888,id);
    }

    @Transactional
    public void addOfJdbc() {
        jdbcTemplate.execute("insert into user (id,age,dept_id) values (1,18,2)");
    }


    @Transactional
    public Integer testCreateTable(){
        String sql =
                "CREATE TABLE `user`" +
                        "(" +
                        "id INT(11) NOT NULL ," +
                        "dept_id INT(11) NOT NULL ," +
                        "user_name VARCHAR(30) NULL DEFAULT NULL ," +
                        "age INT(3) NULL DEFAULT NULL ," +
                        "money DECIMAL (13,3) NULL DEFAULT NULL ,"+
                        "role INT(2) NULL DEFAULT NULL ," +
                        "phone VARCHAR(13) NULL DEFAULT NULL ," +
                        "create_time DATETIME NULL DEFAULT NULL ," +
                        "update_time DATETIME NULL DEFAULT NULL ," +
                        "PRIMARY KEY (id)" +
                        ")";


        new FluentSqlQuery(fluentConnection).execute( "DROP TABLE IF EXISTS `USER`");
        Integer rows = new FluentSqlQuery(fluentConnection).execute(sql);
        return rows;
    }
}
